package main.database;


import main.UI.misc.ErrorHandling;
import main.database.entity.Category;
import main.database.entity.Expense;
import main.database.entity.MyEntity;
import main.database.entity.Product;
import org.hibernate.*;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Restrictions;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: Sknictik
 * Date: 26.11.13
 * Time: 23:11
 * Database manager
 */
public class SQLDatabaseManager {

    private static SessionFactory factory;

    //TODO Import database
    //TODO Export database
    public SQLDatabaseManager() {
        Configuration config = new Configuration();
        try {
            config.configure(new File("./hibernate.cfg.xml"));
        }
        catch (HibernateException ex) {
            ErrorHandling.showError(ex, "Неправильные настройки hibernate.cfg.xml", true);
        }

        //Schema existence check
        try {
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/",
                    config.getProperty("hibernate.connection.username"),
                    config.getProperty("hibernate.connection.password"));
            Statement stmt = conn.createStatement();
            stmt.execute("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA " +
                    "WHERE SCHEMA_NAME = 'domestic_accounting_db'");
            //If there is no schema
            if (!stmt.getResultSet().next())
                createNewSchema(conn);
        }
        catch (SQLException ex) {
            ErrorHandling.showError(ex, "Ошибка при инициализации менеджера базы данных. Проверьте настройки hibernate.cfg.xml", true);
            ex.printStackTrace();
        }

        try {
            ServiceRegistry serviceRegistry = new ServiceRegistryBuilder().applySettings(config.getProperties()).buildServiceRegistry();
            factory = config.buildSessionFactory(serviceRegistry);
        }
        catch (HibernateException e) {
            ErrorHandling.showError(e, "Ошибка при установлении соединения с базой данных", true);
        }

    }


    private void createNewSchema(Connection conn) {
        ScriptRunner runner = new ScriptRunner(conn, false, true);
        BufferedReader reader = null;
        try {
            reader = new BufferedReader(new FileReader(new File("src/create_database_schema.sql")));
        }
        catch (FileNotFoundException ex) {
            ErrorHandling.showError(ex, ex.getMessage(), true);
        }
        try {
            runner.runScript(reader);
        }
        catch (IOException ex) {
            ErrorHandling.showError(ex, "Ошибка чтения сценария", true);
        }
        catch (SQLException ex) {
            ErrorHandling.showError(ex, "Ошибка исполнения сценария", true);
        }
    }


    public List getAllEntriesFromDB(String entityName) throws DatabaseException {
        Session session = factory.openSession();
        List entries = new ArrayList<MyEntity>();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            entries = session.createQuery("FROM " + entityName).list();
            tx.commit();
        }
        catch (HibernateException e) {
            if (tx!=null) tx.rollback();
            throw new DatabaseException("Can't load " + entityName + " from database.");
        }finally {
            session.close();
        }
        return entries;
    }


    public List getExpensesFromDB(String filter, Date startDate,
                                  Date endDate) throws DatabaseException {
        List entries = new ArrayList<MyEntity>();
        Session session = factory.openSession();
        try {
            Criteria criteria = session.createCriteria(Expense.class)
            .add(Restrictions.between("date", startDate, endDate));
            if (filter.equals(Category.NO_CATEGORY)) {
                criteria.createAlias("product", "p")
                    .add(Restrictions.isNull("p.category"));
            }
            else if (!filter.equals("Все")) {
                criteria.createAlias("product", "p");
                criteria.createAlias("p.category", "c")
                    .add(Restrictions.disjunction()
                            //TODO If category is null search doesn't go through to product name for some reason
                            .add(Restrictions.eq("p.name", filter))
                            .add(Restrictions.eq("c.name", filter))
                    );
            }

            entries = criteria.list();
        }catch (HibernateException e) {
            throw new DatabaseException("Can't load expenses from database.");
        }finally {
            session.close();
        }
        return entries;
    }


    public List getFilteredProductsFromDB(String cat) throws DatabaseException {

        Session session = factory.openSession();
        List entries = new ArrayList<MyEntity>();
        Transaction tx = null;
        String productFilterSQLString;
        try {
            tx = session.beginTransaction();

            if (cat.equals("Все"))
                productFilterSQLString = "";
            else if (cat.equals(Category.NO_CATEGORY)) {
                productFilterSQLString = " WHERE product.category is NULL";
            }
            else
                productFilterSQLString = " WHERE product.category.name = '" + cat + "'";

            entries = session.createQuery("FROM Product as product \n" +
                    productFilterSQLString).list();
            tx.commit();
        }catch (HibernateException e) {
            if (tx!=null) tx.rollback();
            throw new DatabaseException("Can't load products from database.");
        }finally {
            session.close();
        }
        return entries;
    }


    /*private MyEntity getEntityByName(String entity, String name) {
        Session session = factory.openSession();
        Transaction transaction = null;
        List foundEntity = null;
        try {
            transaction = session.beginTransaction();
            foundEntity = session.createQuery("FROM "+ entity + " as entity \n" +
                    " WHERE entity.name = " + "'" + name + "'").list();
            transaction.commit();
        }
        catch (HibernateException e) {
            showError(e, "Ошибка при поиске записи");
            session.close();
            return null;
        }
        session.close();
        return (MyEntity) foundEntity.get(0);
    }*/


    /*
        result > 0 - id of founded entry
        result == 0 - no entries with this ID
        result == -1 - some kind of error occurred
        returns first occurence of entity with name
        */
    public Long getIDByName (String entity, String name) throws DatabaseException {
        Session session = factory.openSession();
        Transaction transaction;
        List entityList;
        try {
            transaction = session.beginTransaction();
            entityList = session.createQuery("FROM "+ entity + " as entity \n" +
                    " WHERE entity.name = " + "'" + name + "'").list();
            transaction.commit();
        }
        catch (HibernateException e) {
            throw new DatabaseException("Search error.");
        }
        finally {
            session.close();
        }
        if (entityList.size() == 0)
            return (long)0;
        else return ((MyEntity) entityList.get(0)).getId();
    }

    /*If you need to create new entity pass 0 in id
     otherwise you should pass id number of changed entity to replace it
     */
    public Expense saveExpense(String productName, Date date, double summ, Long id) throws DatabaseException {
        Session session = factory.openSession();
        Transaction transaction = null;
        Expense expense;
        try {
            transaction = session.beginTransaction();
            List<Product> product;

            product = session.createQuery("FROM Product as product \n" +
                    " WHERE product.name = " + "'" + productName + "'").list();

            if (product.size() != 1) {
                throw new DatabaseException("Product is not unique or does not exist at all.");
            }

            expense = new Expense(product.get(0), date, summ);
            if (id != 0)
            {
                expense.setId(id);
                session.update(expense);
            }
            else session.save(expense);
            transaction.commit();
        } catch (HibernateException e) {
            if (transaction != null)
                transaction.rollback();
            throw new DatabaseException("Can't save expense.");
        }
        finally {
            session.close();
        }
        return expense;
    }


    /*If you need to create new entity pass 0 in id
     otherwise you should pass id number of changed entity to replace it
     */
    public Product saveProduct(String name, String catName, Long id) throws DatabaseException {
        Session session = factory.openSession();
        Transaction transaction = null;
        Product product;
        List foundEntity;
        try {
            transaction = session.beginTransaction();
            name = Character.toString(name.charAt(0)).toUpperCase() + name.substring(1);

            Category cat;
            if (!catName.equals(Category.NO_CATEGORY)) {
                foundEntity = session.createQuery("FROM "+ "Category" + " as cat \n" +
                        " WHERE cat.name = :catName").setString("catName", catName).list();

                cat = (Category) foundEntity.get(0);
            }
            else {
                cat = null;
            }
            product = new Product(name, cat);
            if (id != 0)
            {
                product.setId(id);
                session.update(product);
            }
            else session.save(product);
            transaction.commit();
        }
        catch (HibernateException e) {
            if (transaction != null)
                transaction.rollback();
            throw new DatabaseException("Can't save product.");
        }
        finally {
            session.close();
        }
        return product;
    }


    /*If you need to create new entity pass 0 in id
     otherwise you should pass id number of changed entity to replace it
     */
    public Category saveCategory(String name, Long id) throws DatabaseException {
        Session session = factory.openSession();
        Transaction transaction = null;
        Category cat;
        try {
            transaction = session.beginTransaction();
            name = Character.toString(name.charAt(0)).toUpperCase() + name.substring(1);
            cat = new Category(name);
            if (id != 0)
            {
                cat.setId(id);
                session.update(cat);
            }
            else session.save(cat);
            transaction.commit();
        }
        catch (HibernateException e) {
            if (transaction != null)
                transaction.rollback();
            throw new DatabaseException("Can't save category.");
        }
        finally {
            session.close();
        }
        return cat;
    }


    public boolean removeEntityByID(String entityType, long id) throws DatabaseException {
        Session session = factory.openSession();
        Transaction transaction = null;
        try {
            transaction = session.beginTransaction();
            if (!entityType.equals(MyEntity.EXPENSE))
                if (checkIfUsed(entityType, id))
                {
                    ErrorHandling.showError(null, "Невозможно удалить запись. На данный объект уже ссылаются", false);
                    return false;
                }

            MyEntity entity = null;

            if (entityType.equals(MyEntity.PRODUCT)) {
               entity = new Product(id);
            }
            else if (entityType.equals(MyEntity.CATEGORY)) {
               entity = new Category(id);
            }
            else if (entityType.equals(MyEntity.EXPENSE)) {
                entity = new Expense(id);
            }
            session.delete(entity);
            transaction.commit();
            return true;
        }
        catch (HibernateException e) {
            if (transaction != null)
                transaction.rollback();
            throw new DatabaseException("Error deleting entry.");
        }
        finally {
            session.close();
        }
    }


    private boolean checkIfUsed(String entityType, Long idEntityToDelete) throws DatabaseException {
        Session s = factory.openSession();
        Transaction tx = null;
        List found = null;
        try {
            tx = s.beginTransaction();
            if (entityType.equals(MyEntity.PRODUCT))
            {
                found = s.createQuery("FROM Expense as exp" +
                    " WHERE exp.product.id = " + idEntityToDelete).list();
            }
            else if (entityType.equals(MyEntity.CATEGORY))
            {
                found = s.createQuery("FROM Product as product" +
                    " WHERE product.category.id = " + idEntityToDelete).list();
            }
            tx.commit();
        }
        catch (HibernateException e) {
            if (tx!=null)
                tx.rollback();
            throw new DatabaseException("Error usage checking.");
        }
        finally {
            s.close();
        }
        return (found != null ? found.size() : 0) > 0;
    }

    /*private void showError(Exception e, String msg) {
        ErrorHandling.showError(e, msg, false);
    }*/

   /* private boolean needUpdate(String entity){
        if (entity.equals("expense"))
            return (expensesList.size() == 0);
        else if (entity.equals("category"))
            return (categoryList.size() == 0);
        else
            return (productList.size() == 0);
    }*/
}
